﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;
namespace zytech.common
{
    public class Functions
    {
        #region DataTime日期转化为星期几
        /// <summary>
        /// 将DataTime类型的日期转化为星期几
        /// </summary>
        /// <param name="dt"></param>
        /// <returns>返回string类型的星期几</returns>
        public static string ConvertDateTime(DateTime dt)
        {
            string DateTimeName = "";

            if (dt.DayOfWeek == DayOfWeek.Friday)
            {
                DateTimeName = "星期五";
            }
            else if (dt.DayOfWeek == DayOfWeek.Monday)
            {
                DateTimeName = "星期一";
            }
            else if (dt.DayOfWeek == DayOfWeek.Saturday)
            {
                DateTimeName = "星期六";
            }
            else if (dt.DayOfWeek == DayOfWeek.Sunday)
            {
                DateTimeName = "星期日";
            }
            else if (dt.DayOfWeek == DayOfWeek.Thursday)
            {
                DateTimeName = "星期四";
            }
            else if (dt.DayOfWeek == DayOfWeek.Tuesday)
            {
                DateTimeName = "星期二";
            }
            else if (dt.DayOfWeek == DayOfWeek.Wednesday)
            {
                DateTimeName = "星期三";
            }

            return DateTimeName;

        }
        #endregion

        #region 使用SQL语句填充DataTable
        /// <summary>
        /// 使用SQL查询语句填充DataTable
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        /// <returns>返回DataTable类型</returns>
        public static DataTable getDatatable(string sql, string SqlConnStr)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, SqlConnStr);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0];
            return dt;
        }
        #endregion

        #region 执行数据库的增删改命令
        /// <summary>
        /// 执行数据库的增删改命令
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        /// <returns>执行结果影响的行数，返回-1则表示执行失败，返回其他则表示成功</returns>
        public static int ExecuteSql(string sql, string SqlConnStr)
        {
            //ExecuteNonQuery方法的返回值：
            //对于Insert,Update,Delete命令返回影响的行数
            //对于其他类型语句返回-1，执行失败发生回滚也返回-1
            //因此用是否返回-1判断执行失败与否
            int result;
            SqlConnection conn = new SqlConnection(SqlConnStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        #endregion

        #region DataGridView数据绑定
        /// <summary>
        /// 对dataGridView进行数据绑定
        /// </summary>
        /// <param name="dataGrdv">要绑定的DataGridView</param>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        public static void Bound(DataGridView dataGrdv, string sql, string SqlConnStr)
        {
            SqlConnection conn = new SqlConnection(SqlConnStr);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "ds");
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
            dataGrdv.DataSource = ds.Tables[0].DefaultView;
        }
        #endregion

        #region 使用SQL语句填充Dataset
        /// <summary>
        /// 使用SQL语句填充Dataset
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        /// <returns>返回DataSet类型</returns>
        public static DataSet getDataset(string sql, string SqlConnStr)
        {
            SqlConnection conn = new SqlConnection(SqlConnStr);
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        #endregion

        #region DataGridView数据导出EXCEL
        /// <summary>
        /// 将DataGridView数据导出到EXCEL，注意调用此方法后要用系统垃圾回收器来回收资源，关闭EXCEL.EXE进程
        /// </summary>
        /// <param name="dataGrdv">要导出的DataGridview</param>
        /// <param name="titleText">生成目标文件的标题</param>
        public static void ExportDataExcel(DataGridView dataGrdv, string titleText)
        {
            int rowcount, columncount;
            rowcount = dataGrdv.RowCount;
            columncount = dataGrdv.ColumnCount;
            if (rowcount == 0)
            {
                MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
            {
                //新建EXCEL对象
                Excel.Application myExcel = new Excel.Application();
                //新建工作簿
                Excel.Workbook workBook = myExcel.Application.Workbooks.Add(true);
                //新建工作表
                Excel.Worksheet worksheet = workBook.ActiveSheet as Excel.Worksheet;

                myExcel.Visible = true;
                //myExcel.Caption = "用户信息";                                     //表名
                //设置标题行
                Excel.Range titleRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columncount + 1]);
                titleRange.Merge(true);
                titleRange.Value2 = titleText;                                     //内容
                titleRange.Font.Name = "宋体";                                      //字体
                titleRange.Font.Size = 18;                                          //字号
                titleRange.Font.Bold = true;                                        //加粗
                titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;     //水平居中
                titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;       //垂直居中
                //表头
                Excel.Range headRange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 5]);
                headRange.Font.Size = 10;
                headRange.Font.Name = "宋体";
                headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                //生成字段名称
                myExcel.Cells[2, 1] = "序号";
                for (int i = 0; i < dataGrdv.ColumnCount; i++)
                {
                    myExcel.Cells[2, i + 2] = dataGrdv.Columns[i].HeaderText;
                }
                //填充数据
                for (int i = 0; i < rowcount; i++)
                {
                    myExcel.Cells[i + 3, 1] = i + 1;
                    for (int j = 0; j < columncount; j++)
                    {
                        Excel.Range contentRange = worksheet.Cells[i + 3, j + 2] as Excel.Range;
                        contentRange.Value2 = dataGrdv[j, i].Value;
                        contentRange.Font.Size = 10;
                        contentRange.Font.Name = "宋体";
                        contentRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                        contentRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    }
                }
            }
        }
        #endregion

        #region 数字大写数字转换
        /// <summary>
        /// 数字大写数字转换
        /// </summary>
        /// <param name="num">字符型小写数字</param>
        /// <returns>大写数字</returns>
        public static string HZ(char num)
        {
            switch (num)
            {
                case '1': return "壹";
                case '2': return "贰";
                case '3': return "叁";
                case '4': return "肆";
                case '5': return "伍";
                case '6': return "陆";
                case '7': return "柒";
                case '8': return "捌";
                case '9': return "玖";
                default: return "零";
            }
        }
        #endregion

        #region 数字金额转换
        /// <summary>
        /// 数字金额转换
        /// </summary>
        /// <param name="DX">字符型小写数字<普通数字</param>
        /// <returns>金额值多少元</returns>
        public static string RY(string DX)
        {
            string R = "";
            int l = DX.Length;
            if (DX == "0") return R;
            int b = 0;
            for (int i = 0; i < DX.Length; i++)
            {
                int m = DX.Length - i;
                R = R + Functions.HZ(DX[i]);
                if (b == 0 && DX[i] == '0' && m != 5 && m != 1 && i < DX.Length - 1)
                {
                    bool p = true;
                    for (int k = i + 1; k < DX.Length; k++)
                    {
                        if (DX[k] == '0') p = false;
                    }
                    if (p == true)
                    {
                        R = R + "零";
                        b = 1;
                    }
                }
                if (m == 8 && DX[i] != '0') R = R + "仟";
                if (m == 7 && DX[i] != '0') R = R + "佰";
                if (m == 6 && DX[i] != '0') R = R + "拾";
                if (m == 5) R = R + "万";
                if (m == 4 && DX[i] != '0') R = R + "仟";
                if (m == 3 && DX[i] != '0') R = R + "佰";
                if (m == 2 && DX[i] != '0') R = R + "拾";
                if (m == 1) R = R + "元";
            }
            return R;
        }
        #endregion

        #region 数字金额转换到分
        /// <summary>
        /// 数字金额转换到分
        /// </summary>
        /// <param name="m">字符型小写数字到分</param>
        /// <returns>金额值多少元角分</returns>
        public static string RMB(string m)
        {
            string DX = m.ToString();
            int XSD = DX.IndexOf(".", 0);
            string XP;
            if (XSD == -1)
            {
                XP = Functions.RY(DX);
            }
            else
            {
                string RX = "";
                string DX1 = DX.Substring(0, XSD);
                XP = Functions.RY(DX1);
                string DX2 = DX.Substring(XSD, DX.Length - XSD).Substring(1);
                if (DX2.Length == 1)
                    if (DX2[0] != '0')
                        RX = Functions.HZ(DX2[0]) + "角零分";
                if (DX2.Length == 2)
                {
                    if (DX2[1] == '0' && DX2[0] == '0')
                    {
                        RX = "整";
                    }
                    else if (DX2[1] != '0' && DX2[0] != '0')
                        RX = Functions.HZ(DX2[0]) + "角" + Functions.HZ(DX2[1]) + "分";
                    else
                        if (DX2[0] == '0')
                            RX = "零" + Functions.HZ(DX2[1]) + "分";
                        else
                            RX = Functions.HZ(DX2[0]) + "角零分";
                }
                XP = XP + RX;
            }

            if (XP.EndsWith("元")) XP += "整";

            return XP.Replace("拾零万", "拾万").Replace("拾零元", "拾元").Replace("仟零零零元整", "仟元整").Replace("佰零零元整", "佰元整").Replace("万零零零零元", "万元整");
        }

        /// <summary>
        /// 自定义的金额转换程序。将阿拉伯数字转换成汉字
        /// </summary>
        /// <param name="je">小写金额</param>
        /// <returns>大写金额</returns>
        public static string jezh(double je)
        {
            int i, k, j;
            int l_xx1;
            string dxhj, dxnr, xx1, zjzh, dxnr1, dxxz;
            bool b_negative;							//是否为否数
            b_negative = false;
            dxhj = "";
            if (je < 0)
            {
                je = -je;
                b_negative = true;
            }
            xx1 = Math.Round(je * 100, 0).ToString();
            dxnr = "壹贰叁肆伍陆柒捌玖零";
            dxnr1 = "分角元拾佰仟万拾佰仟亿拾佰仟";
            l_xx1 = xx1.Length;
            for (i = 1; i <= l_xx1; i++)
            {
                dxxz = "";
                zjzh = "";
                k = int.Parse(xx1.Substring(i - 1, 1)); //		k:为此位的数值
                j = l_xx1 + 1 - i; //		j:第几位，从最高位开始
                zjzh = dxnr1.Substring(j - 1, 1); //   	生成大写位，即‘仟佰拾....')...'
                if (k == 0)
                {

                    k = 10;
                    /*		亿位为零，万位为零且亿位无，元位为零：应保留大写位		*/
                    if ((zjzh == "亿") || ((dxhj.Substring(dxhj.Length - 1, 1) != "亿")
                        && (zjzh == "万"))
                          || (zjzh == "元"))
                    {
                    }

                    else
                    {
                        zjzh = "";
                    }
                }
                else
                {
                    //dxxz = dxnr.Substring(2*k - 1, 2);
                    dxxz = dxnr.Substring(k - 1, 1);//		生成数字大写，即“壹贰叁肆....”
                    /*		位数不相邻，应在数字前加零，以符合习惯		*/
                    if (dxhj.Length > 0)
                    {
                        if (dxhj.Substring(dxhj.Length - 1, 1) != dxnr1.Substring(j, 1)
                            && (dxhj.Length != 0))
                        {
                            dxxz = "零" + dxxz;
                        }
                    }

                }
                dxhj = dxhj + dxxz + zjzh;//   生成大写汉字	
            }
            xx1 = dxhj.Substring(dxhj.Length - 1, 1);		//    没有角分，加“整”以符合习惯
            if (xx1 == "元")
            {
                dxhj = dxhj + "整";
            }
            if (b_negative)
            {
                dxhj = "负" + dxhj;
            }

            return dxhj;
        }

        #endregion

        #region 将金额小数点后两位加上
        /// <summary>
        /// 将金额小数点后两位加上
        /// </summary>
        /// <param name="jr">字符型数值</param>
        /// <returns>到小数点后两位的数值</returns>
        public static string NewJR(string jr)
        {
            if (jr != null)
            {
                int index = jr.IndexOf(".");
                if (index == -1) jr += ".00";
                if (jr.Length - index == 2) jr += "0";
                if (jr.Length - index == 1) jr += "00";

                //jr = jr.ToString().Replace(".00","");
            }
            return jr;
        }
        #endregion

        #region 中文年月日
        /// <summary>
        /// 中文年月日
        /// </summary>
        /// <param name="date">时间类型</param>
        /// <returns>某年某月某日</returns>
        public static string GetChineseDate(DateTime date)
        {
            return date.Year.ToString() + "年" + date.Month.ToString() + "月" + date.Day.ToString() + "日";
        }
        #endregion

        #region 统计汉字和英文字符的个数
        /// <summary>
        /// 统计汉字和英文字符的个数
        /// </summary>
        /// <param name="str">字符串</param>
        /// <returns>字符个数</returns>
        public static int TotalChrNum(string str)
        {
            int[] result = new int[2];
            result[0] = 0;
            result[1] = 0;

            if (str != null && str != "")
            {
                char[] strs = str.ToCharArray();
                for (int i = 0; i < strs.Length; i++)
                {
                    if (System.Text.RegularExpressions.Regex.IsMatch(strs[i].ToString(), "[\u4e00-\u9fa5]"))
                    {
                        result[0] += 2;
                    }
                    else
                    {
                        result[1]++;
                    }
                }
            }

            return result[0] + result[1];
        }
        #endregion

        #region 解析sql,将where条件加入到sql语句中
        /// <summary>
        /// 解析sql,将where条件加入到sql语句中
        /// </summary>
        /// <param name="ls_old1">sql语句</param>
        /// <param name="ls_where">新增的语句</param>
        /// <returns>完整的sql新语句</returns>
        public static string gf_add_where_query_sql(string ls_old1, string ls_where)
        {
            int li_pos, li_pos2;
            string ls_old2, ls_old3, ls_new;
            ls_old3 = "";
            li_pos = ls_old1.IndexOf("order by") - 1;
            li_pos2 = ls_old1.IndexOf("group by") - 1;
            if ((li_pos2 > 0) && (li_pos > 0))
            {
                li_pos = Math.Min(li_pos, li_pos2);
            }
            else
            {
                li_pos = Math.Max(li_pos, li_pos2);
            }
            if (li_pos > 0)
            {

                ls_old2 = ls_old1.Substring(0, li_pos);
                ls_old3 = ls_old1.Substring(ls_old1.Length - (ls_old1.Length - li_pos + 1),
                                            ls_old1.Length - li_pos + 1);
            }
            else
            {
                ls_old2 = ls_old1;
            }
            //where 的前后都是二个空格

            li_pos = ls_old1.ToLower().IndexOf("  where  ");
            if (li_pos > 0)
            {
                ls_old2 = ls_old2.Substring(0, li_pos - 1) + " WHERE " +
                          ls_old2.Substring(ls_old2.Length - (ls_old2.Length - li_pos - 7),
                                            ls_old2.Length - li_pos - 7);
                ls_new = ls_old2 + " and " + ls_where + ls_old3;
            }
            else
            {
                ls_new = ls_old2 + " WHERE " + ls_where + ls_old3;
            }
            return ls_new;
        }
        #endregion

        #region 取数据库服务器时间
        /// <summary>
        /// 取数据库服务器时间
        /// </summary>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        /// <returns>服务器时间的字符串</returns>
        public static string get_sys_time(string SqlConnStr)
        {
            string sql = "select top 1 getDate() from	master..spt_monitor";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, SqlConnStr);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                string d = ds.Tables[0].Rows[0][0].ToString();
                return d;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 生成下拉列表框的数据
        /// <summary>
        /// 生成下拉列表框的数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="SqlConnStr">数据库连接字符串</param>
        /// <param name="cb">下拉列表框</param>
        /// <returns></returns>
        public static ComboBox CreateComboBox(string sql, string SqlConnStr, ComboBox cb)
        {
            DataSet ds = new DataSet();
            ds = zytech.common.Functions.getDataset(sql, SqlConnStr);
            cb.DataSource = ds;
            return cb;
        }
        #endregion

        public static int getMaxid(string table, string c, string conn)
        {
            string sql="select max("+c+") from "+table +";";
            DataSet bb = getDataset(sql, conn);
            return Int32.Parse( bb.Tables[0].Rows[0][0].ToString());
        }

        public static void Encrypt(string connstr)
        {
            Configuration conf = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            conf.ConnectionStrings.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
            conf.Save(ConfigurationSaveMode.Full);
        }

    }
}
